#!/usr/bin/perl

### DB VERSION CONTROL PERL SCRIPT (I will port this to PHP eventually)

use strict;

use DBI;
use Getopt::Long;
use Carp qw(confess cluck);

my %args;

GetOptions(
	'db_name=s' => \$args{'db_name'},
	'db_hostname=s' => \$args{'db_hostname'},
	'db_user=s' => \$args{'db_user'},
	'db_pass=s' => \$args{'db_pass'},
);

# set defaults if they do not exist
if ($args{'db_name'} eq '') { $args{'db_name'} = 'pushLMS' };
if ($args{'db_hostname'} eq '') { $args{'db_hostname'} = 'localhost' };
if ($args{'db_user'} eq '') { $args{'db_user'} = 'root' };
if ($args{'db_pass'} eq '') { $args{'db_pass'} = 'Smeg00Head' };

# connect to the database
my $dbh = connectdb(\%args);

# get the current version number
my $version_number = get_version();
my $latest_number = get_latest_number();

$version_number ++;

while ($version_number <= $latest_number) {

	open(FILE, "$ENV{'PWD'}/updates/$version_number.sql");
	my @file_array = <FILE>;
	close(FILE);

	my $new_file = "use $args{'db_name'};\n";

	foreach my $line (@file_array) {
		$new_file .= $line;
	}

	my $new_file_name = "TEMP_$version_number";

	open(FILE2, ">$ENV{'PWD'}/$new_file_name");
	print FILE2 $new_file;
	close(FILE2);	
	
	warn "Applying version [$version_number.sql] to [$args{'db_name'}]...";

	` mysql -u $args{'db_user'} -p$args{'db_pass'} <  $ENV{'PWD'}/$new_file_name `;

	update_version($version_number);

	$version_number ++;

	`rm $new_file_name `;

}

my $new_version_number = get_version();

print "New Version Number : $new_version_number  \n";

$dbh->disconnect;

exit;

##########
# subroutines
##########

# connect to the database

sub connectdb {

	my ($args) = @_;

	my $dbh = DBI->connect(
	    qq(DBI:mysql:database=$args->{db_name};host=$args->{db_hostname}),
	    $args->{db_user}, $args->{db_pass}, { HandleError => sub { confess(shift) } }) 
	    or die $DBI::errstr;

	return $dbh;

}


sub get_version {

	# select the current version
	my $sth = $dbh->prepare(qq{SELECT version FROM _DB_Version});
 	eval {
   		$sth->execute;
  	};
  	if ($@) {
  		my $version_number = 0;
	  	open(FILE, "$ENV{'PWD'}/base.sql");
		my @file_array = <FILE>;
		close(FILE);
	
		my $new_file = "use $args{'db_name'};\n";
	
		foreach my $line (@file_array) {
			$new_file .= $line;
		}
	
		my $new_file_name = "TEMP_$version_number";
	
		open(FILE2, ">$ENV{'PWD'}/$new_file_name");
		print FILE2 $new_file;
		close(FILE2);	
		
		warn "Applying version [$version_number.sql] to [$args{'db_name'}]...";
	
		` mysql -u $args{'db_user'} -p$args{'db_pass'} <  $ENV{'PWD'}/$new_file_name `;
		`rm $new_file_name `;
	
		$sth = $dbh->prepare(qq{CREATE TABLE `_DB_Version` (`version` int(11) unsigned NOT NULL default '0') ENGINE=InnoDB DEFAULT CHARSET=latin1;});
		$sth->execute;
		return 0
	}else{
		my $version_number = $sth->fetchrow;
		$sth->finish;
		return $version_number;
	}

}

sub update_version {

	my ($version_number) = @_;

	# select the current version
	my $sth = $dbh->prepare(qq{UPDATE _DB_Version SET version=(?)});
	$sth->execute($version_number);
	$sth->finish;

	return $version_number;

}

sub get_latest_number {

	# get a list of the updates directory to see what to apply
	my @sql_dir = `ls $ENV{'PWD'}/updates/`;

	my @number_list;

	foreach my $sql (@sql_dir) {

		my ($number, $ext) = split (/\./, $sql);
		if ($number =~ /\d/g) {
			push @number_list, $number;
		}

	}

	my @sorted_number_list = sort {$a <=> $b} @number_list;

	return $sorted_number_list[$#sorted_number_list];

}
